/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import Entity.CarBook;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author MrKoi
 */
public class CarBookBean {
    Collection ListCarBook,ListCancelCB,alllistof,ListMyCarBook,ListCarBookID,ListSearch,ListCarBookReceived,ListSearchCarID,ListSearchBookID,ListCancelCBCustomer;
    Connect c=new Connect();
    Connection con;
    CallableStatement cl=null;
    public CarBookBean() {
    }
        private void closeCnn(){
        try {
            cl.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(AdminBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    //lay danh sach car book den khach hang
    public Collection ListMyCB(String username)
    {
        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        con=c.getConnection();
        ListMyCarBook=new ArrayList();
       try
       {
           cl=con.prepareCall("{call GetListMyCarBook_sp(?)}");
           cl.setString(1,username);
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               CarBook cb=new CarBook();
               cb.setCarName(rs.getString("CarName"));
               cb.setImage(rs.getString("Image"));
               cb.setBookingID(rs.getInt("BookingID"));
               cb.setDriver(rs.getString("Driver"));
               cb.setPickUpDate(sdf.format(rs.getDate("PickUpDate")));
               cb.setDropOffDate(sdf.format(rs.getDate("DropOffDate")));
               cb.setTime(rs.getString("Time"));
               cb.setTotalMoney(rs.getFloat("TotalMoney"));
               cb.setDateBooking(sdf.format(rs.getDate("DateBooking")));
               ListMyCarBook.add((Object)cb);
           }
           closeCnn();
       }catch (Exception ex){closeCnn();}
        return ListMyCarBook;
    }
    //hien thi chi tiet huy car den khach hang
     public Collection SelectCancelCBCustomer(String user)
    {
         SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        con=c.getConnection();
        ListCancelCBCustomer=new ArrayList();
       try
       {

           cl=con.prepareCall("{call SelectCancelCarBookCustomer_sp(?)}");
           cl.setString(1,user);
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               CarBook cb=new CarBook();
               cb.setCarName(rs.getString("CarName"));
               cb.setImage(rs.getString("Image"));
               cb.setBookingID(rs.getInt("BookingID"));
               cb.setDriver(rs.getString("Driver"));
               cb.setPickUpDate(sdf.format(rs.getDate("PickUpDate")));
               cb.setDropOffDate(sdf.format(rs.getDate("DropOffDate")));
               cb.setTime(rs.getString("Time"));
               cb.setTotalMoney(rs.getFloat("TotalMoney"));
               cb.setTotalRefund(rs.getFloat("TotalRefund"));  
               cb.setDateBooking(sdf.format(rs.getDate("DateBooking")));
               cb.setDateCancel(rs.getString("DateCancel"));
               ListCancelCBCustomer.add((Object)cb);
           }
           closeCnn();
       }catch (Exception ex){closeCnn();}
        return ListCancelCBCustomer;
    }
    //seach car booking theo username
    public Collection SearchCarBookUserName(String key)
    {
        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        con=c.getConnection();
        ListSearch=new ArrayList();
       try
       {
           cl=con.prepareCall("{call SearchCarBookUserName_sp(?)}");
           cl.setString(1,key);
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               CarBook cb=new CarBook();
               cb.setBookingID(rs.getInt("BookingID"));
               cb.setUserName(rs.getString("UserName"));
               cb.setCarID(rs.getString("CarID"));
               cb.setDriver(rs.getString("Driver"));
               cb.setPickUpDate(sdf.format(rs.getDate("PickUpDate")));
               cb.setDropOffDate(sdf.format(rs.getDate("DropOffDate")));
               cb.setTime(rs.getString("Time"));
               cb.setTotalMoney(rs.getFloat("TotalMoney"));
               cb.setDateBooking(sdf.format(rs.getDate("DateBooking")));
               ListSearch.add((Object)cb);
           }
           closeCnn();
       }catch (Exception ex){closeCnn();}
        return ListSearch;
    }
    //search theo  carid trong bang carbooking
    public Collection SearchCarBookCarid(String key)
    {
        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        con=c.getConnection();
        ListSearchCarID=new ArrayList();
       try
       {
           cl=con.prepareCall("{call SearchCarBookCarid_sp(?)}");
           cl.setString(1,key);
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               CarBook cb=new CarBook();
               cb.setBookingID(rs.getInt("BookingID"));
               cb.setUserName(rs.getString("UserName"));
               cb.setCarID(rs.getString("CarID"));
               cb.setDriver(rs.getString("Driver"));
               cb.setPickUpDate(sdf.format(rs.getDate("PickUpDate")));
               cb.setDropOffDate(sdf.format(rs.getDate("DropOffDate")));
               cb.setTime(rs.getString("Time"));
               cb.setTotalMoney(rs.getFloat("TotalMoney"));
               cb.setDateBooking(sdf.format(rs.getDate("DateBooking")));
               ListSearchCarID.add((Object)cb);
           }
           closeCnn();
       }catch (Exception ex){}
        return ListSearchCarID;
    }
    //search theo bookid tong bang carbook
    public Collection SearchCarBookID(String key)
    {
        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        con=c.getConnection();
        ListSearchBookID=new ArrayList();
       try
       {
           int k=Integer.parseInt(key);
           cl=con.prepareCall("{call SearchCarBookID_sp(?)}");
           cl.setInt(1,k);
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               CarBook cb=new CarBook();
               cb.setBookingID(rs.getInt("BookingID"));
               cb.setUserName(rs.getString("UserName"));
               cb.setCarID(rs.getString("CarID"));
               cb.setDriver(rs.getString("Driver"));
               cb.setPickUpDate(sdf.format(rs.getDate("PickUpDate")));
               cb.setDropOffDate(sdf.format(rs.getDate("DropOffDate")));
               cb.setTime(rs.getString("Time"));
               cb.setTotalMoney(rs.getFloat("TotalMoney"));
               cb.setDateBooking(sdf.format(rs.getDate("DateBooking")));
               ListSearchBookID.add((Object)cb);
           }
           closeCnn();
       }catch (Exception ex){closeCnn();}
        return ListSearchBookID;
    }
    //hien thi theo id
    public Collection SelectCarBookID(int id)
    {
         SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
        con=c.getConnection();
        ListCarBookID=new ArrayList();
       try
       {
           cl=con.prepareCall("{call SelectCarBookID(?)}");
           cl.setInt(1,id);
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               CarBook cb=new CarBook();
               cb.setImage(rs.getString("Image"));
               cb.setBookingID(rs.getInt("BookingID"));
               cb.setDriver(rs.getString("Driver"));
               cb.setPickUpDate(sdf.format(rs.getDate("PickUpDate")));
               cb.setDropOffDate(sdf.format(rs.getDate("DropOffDate")));
               cb.setTime(rs.getString("Time"));
               cb.setTotalMoney(rs.getFloat("TotalMoney"));
               cb.setDateBooking(sdf.format(rs.getDate("DateBooking")));
               ListCarBookID.add((Object)cb);
           }
           closeCnn();
       }catch (Exception ex){}
        return ListCarBookID;
    }
      //hien thi car book cua khach da nhan xe
    public Collection SelectCarBookStatus()
    {
        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        con=c.getConnection();
        ListCarBookReceived=new ArrayList();
       try
       {
           cl=con.prepareCall("{call SelectCarBookStatus_sp}");
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               CarBook cb=new CarBook();
               cb.setBookingID(rs.getInt("BookingID"));
               cb.setUserName(rs.getString("UserName"));
               cb.setCarID(rs.getString("CarID"));
               cb.setDriver(rs.getString("Driver"));
               cb.setPickUpDate(sdf.format(rs.getDate("PickUpDate")));
               cb.setDropOffDate(sdf.format(rs.getDate("DropOffDate")));
               cb.setTime(rs.getString("Time"));
               cb.setTotalMoney(rs.getFloat("TotalMoney"));
               cb.setDateBooking(sdf.format(rs.getDate("DateBooking")));
               ListCarBookReceived.add((Object)cb);
           }
           closeCnn();
       }catch (Exception ex){closeCnn();}
        return ListCarBookReceived;
    }
    //hien thi car book cua khach hang dat
    public Collection SelectCarBook()
    {
        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        con=c.getConnection();
        ListCarBook=new ArrayList();
       try
       {
           cl=con.prepareCall("{call SelectCarBook_sp}");
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               CarBook cb=new CarBook();
               cb.setBookingID(rs.getInt("BookingID"));
               cb.setUserName(rs.getString("UserName"));
               cb.setCarID(rs.getString("CarID"));
               cb.setDriver(rs.getString("Driver"));
               cb.setPickUpDate(sdf.format(rs.getDate("PickUpDate")));
               cb.setDropOffDate(sdf.format(rs.getDate("DropOffDate")));
               cb.setTime(rs.getString("Time"));
               cb.setTotalMoney(rs.getFloat("TotalMoney"));
               cb.setDateBooking(sdf.format(rs.getDate("DateBooking")));
               ListCarBook.add((Object)cb);
           }
           closeCnn();
       }catch (Exception ex){closeCnn();}
        return ListCarBook;
    }
    //hien thi tat ca doanh sach khach hang thue xe
     public Collection SelectCancelCB()
    {
         SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        con=c.getConnection();
        ListCancelCB=new ArrayList();
       try
       {
           cl=con.prepareCall("{call SelectCancelCB_sp}");
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               CarBook cb=new CarBook();
               cb.setBookingID(rs.getInt("BookingID"));
               cb.setUserName(rs.getString("UserName"));
               cb.setCarID(rs.getString("CarID"));
               cb.setDriver(rs.getString("Driver"));
               cb.setPickUpDate(sdf.format(rs.getDate("PickUpDate")));
               cb.setDropOffDate(sdf.format(rs.getDate("DropOffDate")));
               cb.setTotalMoney(rs.getFloat("TotalMoney"));
               cb.setTotalRefund(rs.getFloat("TotalRefund"));
               cb.setTime(rs.getString("Time"));
               cb.setDateBooking(sdf.format(rs.getDate("DateBooking")));
               cb.setDateCancel(rs.getString("DateCancel"));
               ListCancelCB.add((Object)cb);
           }
           closeCnn();
       }catch (Exception ex){closeCnn();}
        return ListCancelCB;
    }
    //hien thi car book khach hang huy
     public Collection AlllistOfCarRental()
    {
         SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        con=c.getConnection();
        alllistof=new ArrayList();
       try
       {
           cl=con.prepareCall("{call AlllistOfCarRental_sp}");
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               CarBook cb=new CarBook();
               cb.setBookingID(rs.getInt("BookingID"));
               cb.setUserName(rs.getString("UserName"));
               cb.setCarID(rs.getString("CarID"));
               cb.setDriver(rs.getString("Driver"));
               cb.setPickUpDate(sdf.format(rs.getDate("PickUpDate")));
               cb.setDropOffDate(sdf.format(rs.getDate("DropOffDate")));
               cb.setTotalMoney(rs.getFloat("TotalMoney"));
               cb.setTotalRefund(rs.getFloat("TotalRefund"));
               cb.setTime(rs.getString("Time"));
               cb.setDateBooking(sdf.format(rs.getDate("DateBooking")));
               cb.setCarCancel(rs.getString("CarCancel"));
               alllistof.add((Object)cb);
           }
           closeCnn();
       }catch (Exception ex){closeCnn();}
        return alllistof;
    }
     //insert car book
     public boolean InserCarBook(String user,String carid,String driver,String pick,String drop,String time,float money)
     {
         con=c.getConnection();
         try
         {
             cl=con.prepareCall("{call InserCarBook_sp(?,?,?,?,?,?,?,?,?)}");
             cl.setString(1,user);
             cl.setString(2,carid);
             cl.setString(3,driver);
             cl.setString(4,pick);
             cl.setString(5,drop);
             cl.setString(6,time);
             cl.setFloat(7,money);
             cl.setString(8,"True");
             cl.setString(9,"True");
             int rs=cl.executeUpdate();
             if(rs >0)
             {
                 return true;
             }
           closeCnn();
         }catch (Exception ex){closeCnn();return false;}
         return false;
     }
     //cap nhat huy car book
     public boolean UpCancelCarBook(int bookID,float money,String daycancel)
     {
         con=c.getConnection();
         try
         {
             cl=con.prepareCall("{call UpCancelCarBook(?,?,?)}");
             cl.setFloat("money",money);
             cl.setString("dateCancel",daycancel);
             cl.setInt("bookingId",bookID);
             int rs=cl.executeUpdate();
             if(rs >0)
             {
                 return true;
             }
           closeCnn();
         }catch (Exception ex){closeCnn();return false;}
         return false;
     }
     //delete car book
     public boolean DeleteCarBook(int id)
     {
         con=c.getConnection();
         try
         {
             cl=con.prepareCall("{call DeleteCarBook_sp(?)}");
             cl.setInt(1,id);
             int rs=cl.executeUpdate();
             if(rs >0)
             {
                 return true;
             }
           closeCnn();
         }catch (Exception ex){closeCnn();return false;}
         return false;
     }
     //kiem tra ngay tra xe
     public int getDay(String user,int bookid)
     {
          con=c.getConnection();
          int day=0;
       try
       {
           cl=con.prepareCall("{call getDay_sp(?,?)}");
           cl.setString("userName",user);
           cl.setInt("bookingID",bookid);
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
              day=rs.getInt("day");
           }
           closeCnn();
       }catch (Exception ex){closeCnn();}
        return day;
     }
     public float getMoney(String user,int bookid)
     {
         //Select TotalMoney from CarBookings where UserName='huynh2409' and BookingID=3
           con=c.getConnection();
          float money=0;
       try
       {
           cl=con.prepareCall("{call getMoney_sp(?,?)}");
           cl.setString(1,user);
           cl.setInt(2,bookid);
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
              money=rs.getFloat("TotalMoney");
           }
           closeCnn();
       }catch (Exception ex){closeCnn();}
        return money;
     }
     //cap nhat status booking
      public boolean UpdateQuantity(int bookid,String status)
    {
        con=c.getConnection();
        try
        {
            cl=con.prepareCall("{call UpdateQuantity_sp(?,?)}");
            if(status.equals("True"))
            {
                cl.setString(1,status);
            }
            else if(status.equals("False"))
            {
                 cl.setString(1,status);
            }
            else
            {
                cl.setString(1,status);
            }
            cl.setInt(2,bookid);
            int rs=cl.executeUpdate();
            if(rs > 0)
            {
                return true;
            }
            closeCnn();
        }catch (Exception ex){closeCnn();return false;}
        
        return false;
    }
}
